package com.hnl.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Vector;

import org.slf4j.LoggerFactory;

import com.hnl.core.connection.ConnectionManager;
import com.hnl.core.connection.ConnectionManagerFactory;
import com.hnl.to.Education;

public class DBEducationDAO implements EducationDAO {
	public static final org.slf4j.Logger LOG = LoggerFactory
			.getLogger(DBReferralDAO.class);

	private PreparedStatement saveStmt;
	private PreparedStatement loadStmt;
	private PreparedStatement updateStmt;

	protected DBEducationDAO() throws SQLException {
		// Empty protected constructor to avoid unnecessary initialization
	}

	@Override
	public Education saveEducation(Education record) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		try {
			saveStmt = conn
					.prepareStatement(
							"INSERT INTO TALENT_EDUCATION (talent_ID, degree, school, major, duration, isdelete) VALUES (?,?,?,?,?,false)",
							PreparedStatement.RETURN_GENERATED_KEYS);
			saveStmt.setInt(1, record.getTalent_ID());
			saveStmt.setString(2, record.getDegree());
			saveStmt.setString(3, record.getSchool());
			saveStmt.setString(4, record.getMajor());
			saveStmt.setString(5, record.getDuration());
			saveStmt.executeUpdate();

			ResultSet rs = saveStmt.getGeneratedKeys();
			if (rs.next()) {
				record.setTalent_education_ID(rs.getInt(1));
			}
			rs.close();
			saveStmt.close();
		} catch (SQLException e) {
			LOG.error("ERROR saving education", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return record;
	}

	@Override
	public Collection<Education> loadEducation(Integer talentId) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		Collection<Education> eduCollection = new Vector<Education>();
		try {
			loadStmt = conn
					.prepareStatement("SELECT talent_education_ID, talent_ID, degree, school, major, duration FROM TALENT_EDUCATION WHERE talent_ID=? AND isdelete=false ");
			loadStmt.setInt(1, talentId);
			ResultSet rs = loadStmt.executeQuery();

			while (rs.next()) {
				Education edu = new Education();
				edu.setTalent_education_ID(rs.getInt("talent_education_ID"));
				edu.setTalent_ID(talentId);
				edu.setDegree(rs.getString("degree"));
				edu.setSchool(rs.getString("school"));
				edu.setMajor(rs.getString("major"));
				edu.setDuration(rs.getString("duration"));
				eduCollection.add(edu);
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("ERROR loading education", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return eduCollection;
	}

	@Override
	public void updateEducation(Education record) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		try {
			updateStmt = conn
					.prepareStatement("UPDATE TALENT_EDUCATION set talent_ID=?,degree=?, school=?, major=?, duration=?, isdelete=? WHERE talent_education_ID=?");
			updateStmt.setInt(1, record.getTalent_ID());
			updateStmt.setString(2, record.getDegree());
			updateStmt.setString(3, record.getSchool());
			updateStmt.setString(4, record.getMajor());
			updateStmt.setString(5, record.getDuration());
			updateStmt.setBoolean(6, record.getIsdelete());
			updateStmt.setInt(7, record.getTalent_education_ID());
			updateStmt.executeUpdate();
			updateStmt.close();
		} catch (SQLException e) {
			LOG.error("ERROR updating education", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
	}

	@Override
	public void deleteEducation(Integer eduId) {
		Education edu = loadEducationByID(eduId);
		edu.setIsdelete(true);
		updateEducation(edu);
	}

	@Override
	public Education loadEducationByID(Integer eduId) {
		ConnectionManager dbConnMgr = ConnectionManagerFactory
				.getConnectionManager(ConnectionManagerFactory.HNL);
		Connection conn = dbConnMgr.getConnection();
		Education edu = new Education();
		try {
			loadStmt = conn
					.prepareStatement("SELECT talent_education_ID, talent_ID, degree, school, major, duration FROM TALENT_EDUCATION WHERE talent_education_ID=? AND isdelete=false ");
			loadStmt.setInt(1, eduId);
			ResultSet rs = loadStmt.executeQuery();

			while (rs.next()) {
				edu.setTalent_education_ID(rs.getInt("talent_education_ID"));
				edu.setTalent_ID(rs.getInt("talent_ID"));
				edu.setDegree(rs.getString("degree"));
				edu.setMajor(rs.getString("major"));
				edu.setSchool(rs.getString("school"));
				edu.setDuration(rs.getString("duration"));
			}
			rs.close();
			loadStmt.close();
		} catch (SQLException e) {
			LOG.error("ERROR loading education", e);
		} finally {
			dbConnMgr.releaseConnection(conn);
		}
		return edu;
	}

}
